package server.demo.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import server.demo.entity.Ticket;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface TicketRepository extends JpaRepository<Ticket, Long> {
    List<Ticket> findByOrderId(Long orderId);
    
    List<Ticket> findByOrderUserIdOrderByCreatedAtDesc(Long userId);
    
    @Query("SELECT t FROM Ticket t WHERE t.train.id = :trainId AND t.ticketStatus = 'VALID'")
    List<Ticket> findValidTicketsByTrainId(@Param("trainId") Long trainId);
    
    @Query("SELECT COUNT(t) FROM Ticket t WHERE t.seat.id = :seatId AND t.ticketStatus IN ('VALID', 'USED')")
    Long countOccupiedBySeatId(@Param("seatId") Long seatId);
    
    // 统计方法
    long countByTicketStatus(Ticket.TicketStatus ticketStatus);
    
    long countByCreatedAtBetween(LocalDateTime start, LocalDateTime end);
    
    @Query("SELECT CONCAT(ss.stationName, ' → ', es.stationName), COUNT(t) FROM Ticket t " +
           "JOIN t.startStation ss JOIN t.endStation es " +
           "GROUP BY ss.stationName, es.stationName ORDER BY COUNT(t) DESC")
    List<Object[]> findPopularRoutes(int limit);
    
    // 查找与特定车站相关的票务记录
    @Query("SELECT t FROM Ticket t WHERE t.startStation.id = :startStationId OR t.endStation.id = :endStationId")
    List<Ticket> findByStartStationIdOrEndStationId(@Param("startStationId") Long startStationId, @Param("endStationId") Long endStationId);
    
    // 查找与特定车次相关的票务记录
    List<Ticket> findByTrainId(Long trainId);
}